﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

namespace WebApplication1
{
    public partial class se_cd : System.Web.UI.Page
    {
        SqlConnection cnn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True");
        protected void Page_Load(object sender, EventArgs e)
        {
            if (IsPostBack == false)
            {
                cnn.Open();
                SqlCommand cmd = new SqlCommand("select c.cdno,c.name,c.publish,c.year,c.copies,c.price from cdinfo c", cnn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                ds.Clear();
                da.Fill(ds, "cd");
                errlbl.Visible = false;
                GridView1.Visible = true;
                GridView1.DataSource = ds;
                GridView1.DataBind();

                SqlCommand cmd1 = new SqlCommand("select distinct lang from cdinfo", cnn);
                SqlDataReader dr1 = cmd1.ExecuteReader();
                while (dr1.Read())
                    DropDownList1.Items.Add(Convert.ToString(dr1.GetString(0)));
                dr1.Close();
                cnn.Close();
                errlbl.Visible = false;
            }
        }
        protected void DropDownList1_SelectedIndexChanged1(object sender, EventArgs e)
        {
            DropDownList2.Items.Clear();
            cnn.Open();
            SqlCommand cmd = new SqlCommand("select distinct cat from cdinfo where lang='" + DropDownList1.SelectedItem + "'", cnn);
            SqlDataReader dr3 = cmd.ExecuteReader();
            while (dr3.Read())
                DropDownList2.Items.Add(Convert.ToString(dr3.GetString(0)));
            dr3.Close();
            cnn.Close();
        }

        protected void DropDownList2_SelectedIndexChanged1(object sender, EventArgs e)
        {
            DropDownList3.Items.Clear();
            cnn.Open();
            SqlCommand cmd = new SqlCommand("select distinct name from cdinfo where cat='" + DropDownList2.SelectedItem + "'", cnn);
            SqlDataReader dr2 = cmd.ExecuteReader();
            while (dr2.Read())
                DropDownList3.Items.Add(Convert.ToString(dr2.GetString(0)));
            dr2.Close();
            cnn.Close();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            cnn.Open();
            SqlCommand cmd = new SqlCommand("select c.cdno,c.name,c.publish,c.year,c.copies,c.price from cdinfo c where c.name='" + DropDownList3.SelectedItem + "'", cnn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            ds.Clear();
            da.Fill(ds, "cd");
            if (ds.Tables["cd"].Rows.Count <= 0)
            {
                errlbl.Visible = true;
                errlbl.Text = "No such results";
                GridView1.Visible = false;
            }
            else
            {
                errlbl.Visible = false;
                GridView1.Visible = true;
                GridView1.DataSource = ds;
                GridView1.DataBind();
            }
            cnn.Close();
        }

    }
}